const express = require('express') //导入插件
const app = express() //对插件进行实例化
const port = 3001 //定义变量, 常量 PI
const db = require('dmdb') //导入dmdb的驱动
var pool = null;

//导入跨域插件


const cors = require('cors')
app.use(cors())
app.use(express.json())
//支持post传输数据
//允许跨域

//路由
app.get('/', (req, res) => {
    //req是请求参数
    //res是返回给请求方的数据
    console.log(req.query)
    res.send('TEST')
})
//启动服务器， 监听某个端口
app.listen(port, () => {
    //``反引号，模板字符串
    console.log(`服务器启动成功，打开http://127.0.0.1:${port}访问服务器地址`)
})
//http://127.0.0.1:3000/api/getAllDept访问数据库，也是通过路由实现
app.get('/api/getAllDept', async (req, res) => {
    //访问数据库的
    //1.初始化连接池
    pool = await createPool();
    //2.从池里面取连接
    var conn = await getConnection();
    //3.执行查询语句
    // var queryStr = "SELECT * FROM DMHR.DEPARTMENT;"
    var queryStr ="SELECT A.DEPARTMENT_ID ,A.DEPARTMENT_NAME,B.STREET_ADDRESS FROM DMHR.DEPARTMENT A,DMHR.LOCATION B  WHERE  A.LOCATION_ID==B.LOCATION_ID;" 
    var resultSetTemp = await conn.execute(queryStr, {}, {resultSet: true})
    //4.整理查询结果
    var tempRe = await resultSetTemp.resultSet.getRows(5);
    //5.返回查询结果

    var jsonTempRe = {
      "status":200,
      "message":"查询成功",
      "data":[]
    }
    tempRe.forEach(element => {
      let temp = {
        "deptId": element[0],
        "deptName": element[1],
        "location": element[2]
      }
      
      jsonTempRe.data.push(temp);
    });

    res.send(jsonTempRe);
})

app.get('/api/delDept',async (req, res) => {
    console.info(req.query.deptId);
    let deptId = req.query.deptId;
    let delStr = `DELETE FROM DMHR.DEPARTMENT WHERE DEPARTMENT_ID=${deptId};commit;`
    
    //1.初始化连接池
    pool = await createPool();
    //2.从池里面取连接
    var conn = await getConnection();
    //3.执行删除语句

    var result = null 
    try {
        result = await conn.execute(delStr, {}, {resultSet: true})
        // result =  result.resultSet;
    }catch(err) {
        console.log(err)
        result = {
            status: 501,
            message: err.message
        }
    } finally {
        await conn.close()
        await pool.close()
        res.json(result)
    }
})

//添加新的路由
app.post("/api/getDeptById",async (req, res) => {
    let deptId = req.body.deptId
    //调用数据查询
    var queryStr =`SELECT A.DEPARTMENT_ID ,A.DEPARTMENT_NAME,B.STREET_ADDRESS FROM DMHR.DEPARTMENT A,DMHR.LOCATION B  WHERE DEPARTMENT_ID='${deptId}' AND A.LOCATION_ID=B.LOCATION_ID;`
    //1.初始化连接池
    pool = await createPool();
    //2.从池里面取连接
    var conn = await getConnection();
    //3.执行 查询语句

    var result = {
        status:200, // 查询正常
        message:"查询成功",
        data:{}
    }
    try {
        let resultTemp = await conn.execute(queryStr, {}, {resultSet: true})
        let resultSetTemp = await resultTemp.resultSet.getRow()
        result.data.deptId = resultSetTemp[0]
        result.data.deptName = resultSetTemp[1]
        result.data.deptLocation = resultSetTemp[2]
        // result =  result.resultSet;
    }catch(err) {
        console.log(err)
        result = {
            status: 501, // 数据库查询异常
            message: err.message
        }
    } finally {
        await conn.close()
        await pool.close()
        res.json(result)
    }


})

//通过部门id更新部门信息
app.post("/api/updateDeptById", async (req,res) => {
    // 接受部门名称和部门编号
    let deptId = req.body.deptId
    let deptName = req.body.deptName

    let updateStr = `UPDATE DMHR.DEPARTMENT SET DEPARTMENT_NAME='${deptName}' WHERE DEPARTMENT_ID=${deptId};commit;`
    
    //1.初始化连接池
    pool = await createPool();
    //2.从池里面取连接
    var conn = await getConnection();
    //3.执行删除语句

    var result = {
        status:0,
        message: '更新成功'
    }
    try {
        await conn.execute(updateStr, {}, {resultSet: true})
        // result =  result.resultSet;
    }catch(err) {
        console.log(err)
        result = {
            status: 501,
            message: err.message
        }
    } finally {
        await conn.close()
        await pool.close()
        res.json(result)
    }
    
})

// 创建连接池
async function createPool() {
    try {
        return await db.createPool({
            connectString: "dm://SYSDBA:SYSDBA123@localhost:5236?autoCommit=false&loginEncrypt=false",
            poolMax: 10,
            poolMin: 1
        });
    } catch (err) {
        throw new Error("createPool error: " + err.message);
    }
}
//获取数据库连接
async function getConnection() {
    try {
        return await pool.getConnection();
    } catch (err) {
        throw new Error("getConnection error: " + err.message);
    }
}
